﻿using System;
using System.Data;
using System.Data.OleDb;//类似JDBC
// 07301061 
// 董敬超
// 2010-6-4
namespace VideoWorld
{
    /// <summary>
    /// 简单的数据库访问类，封装了访问数据库的简单功能，适合小项目使用。
    /// </summary>
    public class SQLTools
    {
        String m_connectionString = String.Empty;//引用类型的空

        [ThreadStatic]//线程，在整个类中只有一份
        static Exception m_lastException = null;

        /// <summary>
        /// 构造函数，从这里传入连接字符串。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public SQLTools(String connectionString)
        {
            m_connectionString = connectionString;
        }

        /// <summary>
        /// 最后一次操作产生的异常，如果最后一次操作成功，则为null
        /// </summary>
        public Exception LastException
        {
            get
            {
                return m_lastException;
            }
        }

        /// <summary>
        /// 异常的处理，如果是DEBUG模式则直接抛出异常，Release则把异常保存下来
        /// </summary>
        /// <param name="e">异常</param>
        void onException(Exception e)
        {
#if DEBUG
            throw e;
#else
			m_lastException = e;
#endif
        }

        /// <summary>
        /// 方法ExecuteNoQuery，执行不需要结果集的SQL语句
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <returns>成功返回true，否则false</returns>
        public bool ExecuteNoQuery(String sql)
        {
            m_lastException = null;

            OleDbConnection conn = new OleDbConnection(m_connectionString);//得到连接

            try
            {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand(sql, conn); //cmd相当于statement

                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                onException(e);
                return false;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return true;
        }

        /// <summary>
        /// 方法ExecuteScalar，执行不需要结果集的SQL语句
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <returns>成功返回true，否则false</returns>
        public Object ExecuteScalar(String sql)
        {
            m_lastException = null;

            OleDbConnection conn = new OleDbConnection(m_connectionString);

            try
            {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand(sql, conn);

                return cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                onException(e);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return null;
        }

        /// <summary>
        /// 方法DataTableFromSQL，从SQL语句得到DataTable
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <returns>成功返回DataTable，否则返回null</returns>
        public DataTable DataTableFromSQL(String sql)
        {
            m_lastException = null;

            try
            {
                OleDbDataAdapter ad = new OleDbDataAdapter(sql, m_connectionString);

                DataSet ds = new DataSet();//相当于
                ad.Fill(ds);

                //返回第一个非空的DataTable
                foreach (DataTable dt in ds.Tables)
                {
                    if (dt.Rows.Count > 0)
                    {
                        return dt;
                    }
                }
            }
            catch (Exception e)
            {
                onException(e);
            }

            return null;
        }

        /// <summary>
        /// 方法DataSetFromSQL，从SQL语句得到DataSet
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <returns>成功返回DataSet，否则返回null</returns>
        public DataSet DataSetFromSQL(String sql)
        {
            m_lastException = null;

            try
            {
                OleDbDataAdapter ad = new OleDbDataAdapter(sql, m_connectionString);

                DataSet ds = new DataSet();
                ad.Fill(ds);

                return ds;
            }
            catch (Exception e)
            {
                onException(e);
            }

            return null;
        }

        /// <summary>
        /// 方法StringFromSQL，从SQL语句得到String，这个值是结果集的第一行第一列的值
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <returns>成功返回String，否则返回null</returns>
        public String StringFromSQL(String sql)
        {
            DataTable dt = DataTableFromSQL(sql);
            if (dt != null && dt.Rows.Count == 1)
            {
                return Convert.ToString(dt.Rows[0][0]);
            }

            return null;
        }

        /// <summary>
        ///方法Int32FromSQL，从SQL语句得到Int32，这个值是结果集的第一行第一列的值
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <param name="invalidValue">无效值，用作出错时的返回值</param>
        /// <returns>成功返回Int32，否则返回invalidValue的值</returns>
        public Int32 Int32FromSQL(String sql, Int32 invalidValue)
        {
            Object obj = ExecuteScalar(sql);
            if (obj != null)
            {
                try
                {
                    return Convert.ToInt32(obj);
                }
                catch (Exception e)
                {
                    onException(e);
                }
            }

            return invalidValue;
        }
    }
}
